package model;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Class allowing to create a database SQLite
 * @author jean
 *
 */
public class MySQLite extends SQLiteOpenHelper {
	
	// Music COL
	private static final String TABLE_MUSIC = "music";
	private static final String COL_ID_MUSIC = "id";
	private static final String COL_FILENAME = "filename";
	private static final String COL_PATHNAME = "pathname";
	private static final String COL_TITLE = "title";
	private static final String COL_TRACKNUMBER = "trackNumber";
	private static final String COL_NBPLAY = "nbPlay";
	private static final String COL_ISLIKE = "isLike";
	private static final String COL_IDARTIST = "idArtist";
	private static final String COL_IDALBUM = "idAlbum";
	private static final String COL_IDGENRE = "idGenre";
	private static final String COL_IDPLAYLIST = "idPlaylist";
	// Artist COL
	private static final String TABLE_ARTIST = "artist";
	private static final String COL_ID_ARTIST = "id";
	private static final String COL_NAMEARTIST = "name";
	// Album COL
	private static final String TABLE_ALBUM = "album";
	private static final String COL_ID_ALBUM = "id";
	private static final String COL_NAMEALBUM = "name";
	private static final String COL_ALBUMCOVER = "albumCover";
	//private static final String COL_IDARTIST = "idArtist";
	// Playlist COL
	private static final String TABLE_PLAYLIST = "playlist";
	private static final String COL_ID_PLAYLIST = "id";
	private static final String COL_NAMEPLAYLIST = "name";
	// Genre COL
	private static final String TABLE_GENRE = "genre";
	private static final String COL_ID_GENRE = "id";
	private static final String COL_NAMEGENRE = "name";
	
	private static final String BDD_MUSIC = 	"CREATE TABLE " + TABLE_MUSIC + " (" +
												COL_ID_MUSIC + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
												COL_FILENAME + " VARCHAR(255) NOT NULL, " +
												COL_PATHNAME + " VARCHAR(255) NOT NULL, " +
												COL_TITLE + " VARCHAR(255) NOT NULL, " +
												COL_TRACKNUMBER + " VARCHAR(255) , " +
												COL_NBPLAY + " INT, " +
												COL_ISLIKE + " BOOLEAN, " +
												COL_IDARTIST + " INT, " +
												COL_IDALBUM + " INT, " +
												COL_IDPLAYLIST + " INT, " +
												COL_IDGENRE + " INT, " +
												"FOREIGN KEY(" + COL_IDARTIST + ") REFERENCES " + TABLE_ARTIST + "(" + COL_ID_ARTIST + "), " +
												"FOREIGN KEY(" + COL_IDALBUM + ") REFERENCES " + TABLE_ALBUM + "(" + COL_ID_ALBUM + "), " +
												"FOREIGN KEY(" + COL_IDPLAYLIST + ") REFERENCES " + TABLE_PLAYLIST + "(" + COL_ID_PLAYLIST + "), " +
												"FOREIGN KEY(" + COL_IDGENRE + ") REFERENCES " + TABLE_GENRE + "(" + COL_ID_GENRE + "));";
	
	private static final String BDD_ARTIST = 	"CREATE TABLE " + TABLE_ARTIST + " (" +
												COL_ID_ARTIST + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
												COL_NAMEARTIST + " VARCHAR(255) NOT NULL);";
	private static final String BDD_ALBUM = 	"CREATE TABLE " + TABLE_ALBUM + " (" +
												COL_ID_ALBUM + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
												COL_NAMEALBUM + " VARCHAR(255) NOT NULL, " +
												COL_ALBUMCOVER + " BLOB, " + 
												COL_IDARTIST + " INT, " +
												"FOREIGN KEY(" + COL_IDARTIST + ") REFERENCES " + TABLE_ARTIST + "(" + COL_ID_ARTIST + "));";
	private static final String BDD_PLAYLIST = 	"CREATE TABLE " + TABLE_PLAYLIST + " (" +
												COL_ID_PLAYLIST + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
												COL_NAMEPLAYLIST + " VARCHAR(255) NOT NULL);";
	private static final String BDD_GENRE = 	"CREATE TABLE " + TABLE_GENRE + " (" +
												COL_ID_GENRE + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
												COL_NAMEGENRE + " VARCHAR(255) NOT NULL);";
											

	public MySQLite(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL(BDD_ARTIST);
		db.execSQL(BDD_ALBUM);
		db.execSQL(BDD_PLAYLIST);
		db.execSQL(BDD_GENRE);
		db.execSQL(BDD_MUSIC);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
		// TODO Auto-generated method stub
		
	}

}
